﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;

namespace Globsite.Globalization.Mvc.DbResources.Repositories
{
    public class SqlServerRepository : RepositoryBase
    {
        #region DbResourceManager

        public override string GetResource(string resourceKey, string resourceSet, string cultureCode = "")
        {
            Debug.WriteLine("SqlServerRepository.GetResource({0}, {1}, {2})", resourceKey, resourceSet, cultureCode);

            if (string.IsNullOrEmpty(resourceKey))
                throw new ArgumentNullException(nameof(resourceKey));
            if (resourceSet == null)
                throw new ArgumentNullException(nameof(resourceSet));
            if (cultureCode == null)
                throw new ArgumentNullException(nameof(cultureCode));

            string result;
            var condition1 = this.IgnoreCase ? "UPPER([Set]) = UPPER(@ResourceSet)" : "[Set] = @ResourceSet";
            var condition2 = this.IgnoreCase ? "UPPER([Key]) = UPPER(@ResourceKey)" : "[Key] = @ResourceKey";
            var query = string.Format("SELECT TOP 1 [Value] FROM [{0}] WHERE {1} AND {2} AND [CultureCode] = @ResourceCultureCode;", this.ResourceTable, condition1, condition2);
            using (var connection = new SqlConnection(this.ConnectionString))
            {
                var command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@ResourceSet", resourceSet);
                command.Parameters.AddWithValue("@ResourceKey", resourceKey);
                command.Parameters.AddWithValue("@ResourceCultureCode", cultureCode);

                connection.Open();
                result = (string)command.ExecuteScalar();
            }

            return result;
        }

        public override IDictionary<string, string> GetResources(string resourceSet, string cultureCode = "")
        {
            Debug.WriteLine(string.Format("SqlServerRepository.GetResources({0}, {1})", resourceSet, cultureCode));

            if (resourceSet == null)
                throw new ArgumentNullException("resourceSet");
            if (cultureCode == null)
                throw new ArgumentNullException("cultureCode");

            var result = new Dictionary<string, string>();
            var condition = this.IgnoreCase ? "UPPER([Set]) = UPPER(@ResourceSet)" : "[Set] = @ResourceSet";
            var query = string.Format("SELECT [Key], [Value] FROM [{0}] WHERE {1} AND [CultureCode] = @ResourceCultureCode;", this.ResourceTable, condition);
            using (var connection = new SqlConnection(this.ConnectionString))
            {
                var command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@ResourceSet", resourceSet);
                command.Parameters.AddWithValue("@ResourceCultureCode", cultureCode);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var key = reader.GetString(0);
                        var value = reader.GetString(1);
                        result.Add(key, value);
                    }
                }
            }

            return result;
        }

        public override void SynchronizeResources(IDictionary<string, string> resources, string resourceSet, string cultureCode = "")
        {
            Debug.WriteLine(string.Format("SqlServerRepository.SynchronizeResources({0}, {1}, {2})", resources, resourceSet, cultureCode));

            if (resources == null)
                throw new ArgumentNullException("resources");
            if (resourceSet == null)
                throw new ArgumentNullException("resourceSet");
            if (cultureCode == null)
                throw new ArgumentNullException("cultureCode");

            // If no resource does not exist delete all records from database.
            if (resources.Count == 0)
            {
                var query = string.Format("DELETE FROM [{0}];", this.ResourceTable);
                executeNonQuery(query);
            }
            else
            {
                // Create the values list for a query.
                var values = string.Empty;
                var index = 1;
                foreach (var resource in resources)
                {
                    values += string.Format("({0}, {1}, {2}, {3})", resourceSet, resource.Key, cultureCode, resource.Value);
                    if (index < resources.Count)
                        values += ", ";
                    index++;
                }
                // Create the base query.
                var query = string.Format("MERGE INTO [{0}] AS T"
                            + " USING (VALUES " + values + ")"
                            + " AS S ([Set], [Key], [CultureCode], [Value]) ON T.[Set] = S.[Set] AND T.[Key] = S.[Key] AND T.[CultureCode] = S.[CultureCode]"
                            + " WHEN MATCHED THEN UPDATE SET T.[Value] = S.[Value]"
                            + " WHEN NOT MATCHED BY TARGET THEN INSERT ([Set], [Key], [CultureCode], [Value]) VALUES (S.[Set], S.[Key], S.[CultureCode], S.[Value])"
                            + " WHEN NOT MATCHED BY SOURCE THEN DELETE;", this.ResourceTable);

                executeNonQuery(query);
            }
        }

        public override void AddResource(string resourceKey, string resourceValue, string resourceSet, string cultureCode = "")
        {
            Debug.WriteLine(string.Format("SqlServerRepository.AddResource({0}, {1}, {2}, {3})", resourceKey, resourceValue, resourceSet, cultureCode));

            if (string.IsNullOrEmpty(resourceKey))
                throw new ArgumentNullException("resourceKey");
            if (resourceSet == null)
                throw new ArgumentNullException("resourceSet");
            if (cultureCode == null)
                throw new ArgumentNullException("cultureCode");


            using (var connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();

                // If the resource is for an invariant culture.
                if (cultureCode != CultureInfo.InvariantCulture.Name)
                {
                    // If the resource for an invariant culture does not exist then create a new one.
                    var query1 = string.Format("IF NOT EXISTS (SELECT * FROM [{0}] WHERE [Set] = @ResourceSet AND [Key] = @ResourceKey AND [CultureCode] = @ResourceCultureCode) " +
                                               "INSERT INTO [{0}] ([Set], [Key], [CultureCode], [Value]) VALUES (@ResourceSet, @ResourceKey, @ResourceCultureCode, @ResourceValue);", this.ResourceTable);
                    var command1 = new SqlCommand(query1, connection);
                    command1.Parameters.AddWithValue("@ResourceSet", resourceSet);
                    command1.Parameters.AddWithValue("@ResourceKey", resourceKey);
                    command1.Parameters.AddWithValue("@ResourceCultureCode", CultureInfo.InvariantCulture.Name);
                    command1.Parameters.AddWithValue("@ResourceValue", null);
                    command1.ExecuteNonQuery();
                }

                // Create a new resource for a specific culture.
                var query2 = string.Format("INSERT INTO [{0}] ([Set], [Key], [CultureCode], [Value]) VALUES(@ResourceSet, @ResourceKey, @ResourceCultureCode, @ResourceValue);", this.ResourceTable);
                var command2 = new SqlCommand(query2, connection);
                command2.Parameters.AddWithValue("@ResourceSet", resourceSet);
                command2.Parameters.AddWithValue("@ResourceKey", resourceKey);
                command2.Parameters.AddWithValue("@ResourceCultureCode", cultureCode);
                command2.Parameters.AddWithValue("@ResourceValue", resourceValue);
                command2.ExecuteNonQuery();
            }
        }

        #endregion

        #region T4Template

        public override IDictionary<string, IList<string>> GetAllResourceKeys()
        {
            Debug.WriteLine("SqlServerRepository.GetAllResourceKeys()");

            var result = new Dictionary<string, IList<string>>();
            var query = string.Format("SELECT [Set], [Key] FROM [{0}] GROUP BY [Set], [Key] ORDER BY [SET], [KEY];", this.ResourceTable);
            using (var connection = new SqlConnection(this.ConnectionString))
            {
                var command = new SqlCommand(query, connection);
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var set = reader.GetString(0);
                        var key = reader.GetString(1);

                        if (result.ContainsKey(set))
                            result[set].Add(key);
                        else
                            result.Add(set, new List<string> { key });
                    }
                }
            }

            return result;
        }

        public override bool CheckIfResourceTableExists()
        {
            Debug.WriteLine("SqlServerRepository.CheckIfResourceTableExists()");

            using (var connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();
                var tables = connection.GetSchema("Tables");
                foreach (DataRow table in tables.Rows)
                    if (((string)table["table_name"] == this.ResourceTable) && ((string)table["table_type"] == "BASE TABLE"))
                        return true;
            }

            return false;
        }

        public override void CreateResourceTable()
        {
            Debug.WriteLine("SqlServerRepository.CreateResourceTable()");

            var query = string.Format("CREATE TABLE [{0}]" +
                                        "([Set] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, " +
                                        "[Key] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, " +
                                        "[CultureCode] NVARCHAR(14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, " +
                                        "[Value] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, " +
                                        "CONSTRAINT [PK_{0}] PRIMARY KEY ([Set], [Key], [CultureCode]));", this.ResourceTable);

            executeNonQuery(query);
        }

        public override void InsertSampleDataToResourceTable()
        {
            Debug.WriteLine("SqlServerRepository.InsertSampleDataToResourceTable()");

            var query = string.Format("INSERT INTO [{0}] ([Set], [Key], [CultureCode], [Value]) VALUES " +
                                        "(N'Home', N'HelloWorld', N'', N'Hello World!'), (N'Home', N'HelloWorld', N'de', N'Hallo Welt!'), (N'Home', N'HelloWorld', N'es-ES', N'Hola Mundo!'), " +
                                        "(N'Home.Index', N'IndexTitle', N'', N'Index Title'), (N'Home.Index', N'IndexTitle', N'de', N'Index Titel'), (N'Home.Index', N'IndexTitle', N'es-ES', N'Index título'), " +
                                        "(N'Home.About', N'AboutTitle', N'', N'About Title'), (N'Home.About', N'AboutTitle', N'de', N'About Titel'), (N'Home.About', N'AboutTitle', N'es-ES', N'About título')", this.ResourceTable);

            executeNonQuery(query);
        }

        #endregion

        private int executeNonQuery(string query)
        {
            int result;
            using (var connection = new SqlConnection(this.ConnectionString))
            {
                var command = new SqlCommand(query, connection);
                connection.Open();
                result = command.ExecuteNonQuery();
            }

            return result;
        }
    }
}
